﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using TransCend.DB;
using TransCend.Model;
using TransCend.Util.Page;
using log4net;
using Util;

namespace TransCend.DAL
{
    class CommonDAL : ICommonDAL
    {
        protected log4net.ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public static Dictionary<string, ModelSQLUtil> sqlUtilMAP = new Dictionary<string, ModelSQLUtil>();

        public Column GetColumn(string tablename, string columnname)
        {
            if (sqlUtilMAP.ContainsKey(tablename))
            {
                return sqlUtilMAP[tablename].GetModelProperty().GetColumn(columnname);
            }
            return null;
        }

        public ModelSQLUtil GetModelSQLUtil(Type modelType)
        {
            ModelProperty bp = ModelPropertyFactory.GetModelProperty(modelType);
            if (sqlUtilMAP.ContainsKey(bp.GetTableName()))
            {
                return sqlUtilMAP[bp.GetTableName()];
            }
            ModelSQLUtil sqlutil = new ModelSQLUtil(bp);
            sqlUtilMAP[bp.GetTableName()] = sqlutil;
            return sqlutil;
        }

        private object GetRdrObject(SqlDataReader rdr, int i, Column c)
        {
            object obj = null;
            switch (c.Type)
            {
                case DataType.Int: obj = rdr.GetInt32(i); break;
                case DataType.Date: obj = rdr.GetDateTime(i); break;
                case DataType.DateTime: obj = rdr.GetDateTime(i); break;
                case DataType.Decimal: obj = rdr.GetDecimal(i); break;
                case DataType.Float: obj = rdr.GetDouble(i); break;
                case DataType.Bit: obj = rdr.GetBoolean(i); break;
                default: obj = rdr.GetString(i); break;
            }
            return obj;
        }

        private T GetModel<T>(ModelSQLUtil sqlutil, SqlDataReader rdr) where T : BaseModel
        {
            T model = (T)Activator.CreateInstance(typeof(T));
            for (int i = 0; i < sqlutil.GetModelProperty().GetColumns().Count; i++)
            {
                Column c = sqlutil.GetModelProperty().GetColumns()[i];
                if (!rdr.IsDBNull(i))
                {
                    model[c.Alias] = GetRdrObject(rdr, i, c);
                }
            }
            return model;
        }

        private void LogDebug(SqlParameter[] parms)
        {
            if (logger.IsDebugEnabled && parms != null)
            {
                foreach (SqlParameter p in parms)
                {
                    logger.Debug(p.ParameterName + "=" + p.Value + "|" + p.SqlDbType);
                }
            }
        }

        private void LogDebug(object sql)
        {
            logger.Debug(sql);
        }

        public T FindByID<T>(int id) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append(sqlutil.GetSelectSQL());
            sql.Append(" WHERE ").Append(sqlutil.GetModelProperty().GetPk()).Append("=@ID");
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@ID", SqlDbType.Int)
            };
            parms[0].Value = id;
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                if (rdr.Read())
                {
                    return GetModel<T>(sqlutil, rdr);
                }
            }
            return null;
        }

        public T FindByID<T>(string id) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append(sqlutil.GetSelectSQL());
            sql.Append(" WHERE ").Append(sqlutil.GetModelProperty().GetPk()).Append("=@ID");
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@ID", SqlDbType.NVarChar)
            };
            parms[0].Value = id;
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                if (rdr.Read())
                {
                    return GetModel<T>(sqlutil, rdr);
                }
            }
            return null;
        }

        public T FindByProperty<T>(string propertyname, object propertyvalue) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append(sqlutil.GetSelectSQL());
            sql.Append(" WHERE ").Append(propertyname).Append("=@propertyname");
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@propertyname", sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(propertyname)))
            };
            parms[0].Value = propertyvalue;
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                if (rdr.Read())
                {
                    return GetModel<T>(sqlutil, rdr);
                }
            }
            return null;
        }

        public T FindByPropertys<T>(string[] propertynames, object[] propertyvalues) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append(sqlutil.GetSelectSQL());
            sql.Append(" WHERE ");
            for (int i = 0; i < propertynames.Length; i++)
            {
                if (i > 0) sql.Append(" AND ");
                sql.Append(" ").Append(propertynames[i]).Append("=@").Append(propertynames[i]);
            }
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[propertyvalues.Length];
            for (int i = 0; i < propertynames.Length; i++)
            {
                parms[i] = new SqlParameter("@" + propertynames[i], sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(propertynames[i])));
                parms[i].Value = propertyvalues[i];
            }
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                if (rdr.Read())
                {
                    return GetModel<T>(sqlutil, rdr);
                }
            }
            return null;
        }

        public IList<T> FindAll<T>(params OrderBy[] orderBy) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetSelectSQL();
            sql += GetOrderBy(orderBy);
            LogDebug(sql);
            IList<T> list = new List<T>();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, null))
            {
                while (rdr.Read())
                {
                    list.Add(GetModel<T>(sqlutil, rdr));
                }
            }
            return list;
        }

        public IList<T> FindList<T>(string propertyname, object propertyvalue, params OrderBy[] orderBy) where T : BaseModel
        {
            return FindList<T>(new string[] { propertyname }, new object[] { propertyvalue }, orderBy);
        }

        public IList<T> FindList<T>(string[] propertynames, object[] propertyvalues, params OrderBy[] orderBy) where T : BaseModel
        {
            IList<T> list = new List<T>();
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append(sqlutil.GetSelectSQL());
            sql.Append(" WHERE ");
            for (int i = 0; i < propertynames.Length; i++)
            {
                if (i > 0) sql.Append(" AND ");
                sql.Append(" ").Append(propertynames[i]).Append("=@").Append(propertynames[i]);
            }
            sql.Append(GetOrderBy(orderBy));
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[propertyvalues.Length];
            for (int i = 0; i < propertynames.Length; i++)
            {
                parms[i] = new SqlParameter("@" + propertynames[i], sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(propertynames[i])));
                parms[i].Value = propertyvalues[i];
            }
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                while (rdr.Read())
                {
                    list.Add(GetModel<T>(sqlutil, rdr));
                }
            }
            return list;
        }

        public IList<T> FindList<T>(T queryModel, params OrderBy[] orderBy) where T : BaseModel
        {
            IList<T> list = new List<T>();
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder();
            sql.Append(sqlutil.GetSelectSQL());
            // WHERE
            StringBuilder where = new StringBuilder();
            SqlParameter[] parms = null;
            IList<Column> querycolumns = GetQueryColumn(queryModel);
            if (querycolumns.Count > 0)
            {
                where.Append(" WHERE ");
                parms = new SqlParameter[querycolumns.Count];
                for (int i = 0; i < querycolumns.Count; i++)
                {
                    Column c = querycolumns[i];
                    if (i > 0) where.Append(" AND ");
                    if (c.Type == DataType.String || c.Type == DataType.NText)
                    {
                        where.Append(c.Name).Append(" LIKE '%' + @").Append(c.Alias).Append(" + '%'");
                    }
                    else
                    {
                        where.Append(c.Name).Append("=@").Append(c.Alias);
                    }
                    parms[i] = sqlutil.GetSqlParameter(c, queryModel);
                }
            }
            sql.Append(where);
            sql.Append(GetOrderBy(orderBy));
            LogDebug(sql);
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                while (rdr.Read())
                {
                    list.Add(GetModel<T>(sqlutil, rdr));
                }
            }
            return list;
        }

        public IList<T> FindList<T>(int[] ids, params OrderBy[] orderBy) where T : BaseModel
        {
            IList<T> list = new List<T>();
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder();
            sql.Append(sqlutil.GetSelectSQL());
            // WHERE
            StringBuilder where = new StringBuilder();
            ModelProperty bp = ModelPropertyFactory.GetModelProperty(typeof(T));
            where.Append(" where "+bp.GetPk());
            where.Append(" in (");
            foreach (int id in ids)
            {
                where.Append(id + ",");
            }
            where.Remove(where.Length - 1, 1);
            where.Append(")");
            sql.Append(where);
            sql.Append(GetOrderBy(orderBy));
            LogDebug(sql);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString()))
            {
                while (rdr.Read())
                {
                    list.Add(GetModel<T>(sqlutil, rdr));
                }
            }
            return list;
        }

        public int Insert<T>(T model) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetInsertSQL();
            SqlParameter[] parms = sqlutil.GetInsertSqlParameters(model);
            int id = 0;
            LogDebug(sql);
            LogDebug(parms);
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, sql, parms).ToString();
                        id = int.Parse(s);
                        if (sqlutil.GetModelProperty().GetPrimaryKey().Identity)
                        {
                            // 得到主键ID
                            model[sqlutil.GetModelProperty().GetPkColumn().Alias] = id;
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            return id > 0 ? 1 : 0;
        }

        public int InsertReturnId<T>(T model) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetInsertSQL();
            SqlParameter[] parms = sqlutil.GetInsertSqlParameters(model);
            int id = 0;
            LogDebug(sql);
            LogDebug(parms);
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, sql, parms).ToString();
                        id = int.Parse(s);
                        if (sqlutil.GetModelProperty().GetPrimaryKey().Identity)
                        {
                            // 得到主键ID
                            model[sqlutil.GetModelProperty().GetPkColumn().Alias] = id;
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            return id;
        }

        public int Insert<T>(IList<T> models) where T : BaseModel
        {
            if (models.Count <= 0) return 0;
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetInsertSQL();
            int rs = 0;
            LogDebug(sql);
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (T model in models)
                        {
                            SqlParameter[] parms = sqlutil.GetInsertSqlParameters(model);
                            int id = 0;
                            LogDebug(parms);
                            string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, sql, parms).ToString();
                            id = int.Parse(s);
                            if (sqlutil.GetModelProperty().GetPrimaryKey().Identity)
                            {
                                // 得到主键ID
                                model[sqlutil.GetModelProperty().GetPkColumn().Alias] = id;
                            }
                            rs++;
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            return rs;
        }

        public int Update<T>(T model) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetUpdateSQL();
            SqlParameter[] parms = sqlutil.GetUpdateSQLParameter(model);
            LogDebug(sql);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sql, parms);
        }

        public int Delete<T>(int id) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetDeleteSQL();

            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@ID", SqlDbType.Int)
            };
            parms[0].Value = id;
            LogDebug(sql);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sql, parms);
        }

        public int DeleteByProperty<T>(string propertyname, object propertyvalue) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE FROM ");
            sb.Append(sqlutil.GetModelProperty().GetTableName()).Append(sqlutil.GetWithRowLock());
            sb.Append(" WHERE ");
            sb.Append(propertyname);
            sb.Append("=@propertyname");
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@propertyname", sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(propertyname)))
            };
            parms[0].Value = propertyvalue;
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sb.ToString(), parms);
        }

        public int DeleteByPropertys<T>(string[] propertynames, object[] propertyvalues) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder().Append("DELETE FROM ");
            sql.Append(sqlutil.GetModelProperty().GetTableName());
            sql.Append(sqlutil.GetWithRowLock());
            sql.Append(" WHERE ");

            for (int i = 0; i < propertynames.Length; i++)
            {
                if (i > 0) sql.Append(" AND ");
                sql.Append(" ").Append(propertynames[i]).Append("=@").Append(propertynames[i]);
            }
            LogDebug(sql);
            SqlParameter[] parms = new SqlParameter[propertyvalues.Length];
            for (int i = 0; i < propertynames.Length; i++)
            {
                parms[i] = new SqlParameter("@" + propertynames[i], sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(propertynames[i])));
                parms[i].Value = propertyvalues[i];
            }

            LogDebug(parms);
            return SQLHelper.ExecuteSql(sql.ToString(), parms);
        }

        public int Delete<T>(int[] ids) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetDeleteSQL();
            int rs = 0;
            LogDebug(sql);
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (int id in ids)
                        {
                            SqlParameter[] parms = new SqlParameter[] {
                                new SqlParameter("@ID", SqlDbType.Int)
                            };
                            parms[0].Value = id;
                            LogDebug(parms);
                            int rsi = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, parms);
                            if (rsi > 0) rs++;
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            return rs;
        }

        public int Delete<T>(string id) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            string sql = sqlutil.GetDeleteSQL();
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@ID", SqlDbType.NVarChar)
            };
            parms[0].Value = id;
            LogDebug(sql);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sql, parms);
        }

        public int Delete<T>(string DBFied, object value) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE FROM ");
            sb.Append(sqlutil.GetModelProperty().GetTableName()).Append(sqlutil.GetWithRowLock());
            sb.Append(" WHERE ").Append(DBFied).Append("=@para0");
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@para0", sqlutil.GetSqlDbType(sqlutil.GetModelProperty().GetColumn(DBFied)))
            };
            parms[0].Value = value;
            LogDebug(sb);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sb.ToString(), parms);
        }

        public int UpdatePartially<T>(T model, string[] DBFields) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            sb.Append(sqlutil.GetModelProperty().GetTableName()).Append(sqlutil.GetWithRowLock());
            sb.Append(" SET ");
            SqlParameter[] parms = new SqlParameter[DBFields.Length + 1];
            for (int i = 0; i < DBFields.Length; i++)
            {
                if (i > 0) sb.Append(",");
                Column c = sqlutil.GetModelProperty().GetColumn(DBFields[i]);
                sb.Append(DBFields[i]).Append("=@").Append(c.Alias);
                parms[i] = sqlutil.GetSqlParameter(c, model);
            }
            sb.Append(" WHERE ").Append(sqlutil.GetModelProperty().GetPk()).Append("=@").Append(sqlutil.GetModelProperty().GetPkColumn().Alias);
            parms[DBFields.Length] = sqlutil.GetIDSqlParameter(model);
            LogDebug(sb);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sb.ToString(), parms);
        }

        public int UpdateSetp<T>(int id, string DBField) where T : BaseModel
        {
            return UpdateSetp<T>(id, DBField, 1);
        }

        public int UpdateSetp<T>(int id, string DBField, int setp) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            sb.Append(sqlutil.GetModelProperty().GetTableName()).Append(sqlutil.GetWithRowLock());
            sb.Append(" SET ");
            sb.Append(DBField).Append("=").Append(DBField).Append("+").Append(setp);
            sb.Append(" WHERE ").Append(sqlutil.GetModelProperty().GetPk()).Append("=@ID");

            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@ID", SqlDbType.Int)
            };
            parms[0].Value = id;
            LogDebug(sb);
            LogDebug(parms);
            return SQLHelper.ExecuteSql(sb.ToString(), parms);
        }

        public int Merge<T>(T model) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            T updateModel = FindByID<T>(model.GetInt(sqlutil.GetModelProperty().GetPkColumn().Alias).Value);
            List<Column> columns = sqlutil.GetModelProperty().GetUpdateColumns();
            foreach (Column c in columns)
            {
                if (model.ContainsKey(c.Alias) && model[c.Alias] != null)
                {
                    updateModel[c.Alias] = model[c.Alias];
                }
            }
            return Update(updateModel);
        }

        private string GetOrderBy(OrderBy[] orderBy)
        {
            StringBuilder sb = new StringBuilder();
            if (orderBy != null && orderBy.Length > 0)
            {
                sb.Append(" ORDER BY ");
                int i = 0;
                foreach (OrderBy ob in orderBy)
                {
                    if (i > 0) sb.Append(",");
                    sb.Append(ob.Sort);
                    sb.Append(ob.Order == OrderBy.Type.DESC ? " DESC" : "");
                    i++;
                }
            }
            return sb.ToString();
        }

        public PageTools<T> FindPage<T>(int page, int rows, OrderBy[] orderBy) where T : BaseModel
        {
            return FindPage<T>(page, rows, null, orderBy);
        }

        public PageTools<T> FindPage<T>(int page, int rows, T queryModel, OrderBy[] orderBy) where T : BaseModel
        {
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder();
            string orderby = GetOrderBy(orderBy);
            sql.Append("SELECT * from (SELECT *, ROW_NUMBER() OVER ( ").Append(orderby).Append(" ) AS RowNumber FROM (");
            sql.Append(sqlutil.GetSelectSQL());
            // WHERE
            StringBuilder where = new StringBuilder();
            SqlParameter[] parms = null;
            IList<Column> querycolumns = GetQueryColumn(queryModel);
            if (querycolumns.Count > 0)
            {
                where.Append(" WHERE ");
                parms = new SqlParameter[querycolumns.Count];
                for (int i = 0; i < querycolumns.Count; i++)
                {
                    Column c = querycolumns[i];
                    if (i > 0) where.Append(" AND ");
                    // 字符串类型默认进行模糊查询
                    if (c.Type == DataType.String || c.Type == DataType.NText)
                    {
                        where.Append(c.Name).Append(" LIKE '%' + @").Append(c.Alias).Append(" + '%'");
                    }
                    else
                    {
                        where.Append(c.Name).Append("=@").Append(c.Alias);
                    }
                    //where.Append(c.Name).Append("=@").Append(c.Alias);
                    parms[i] = sqlutil.GetSqlParameter(c, queryModel);
                }
            }
            sql.Append(where);
            sql.Append(") as s) as temp WHERE RowNumber BETWEEN ").Append((page - 1) * rows).Append(" AND ").Append(rows);
            sql.Append(orderby);
            IList<T> list = new List<T>();
            LogDebug(sql);
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms))
            {
                while (rdr.Read())
                {
                    list.Add(GetModel<T>(sqlutil, rdr));
                }
            }
            string csql = new StringBuilder().Append("SELECT COUNT(1) FROM ").Append(sqlutil.GetModelProperty().GetTableName()).Append(sqlutil.GetWithNoLock()).Append(where).ToString();
            int counts = 0;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, csql, parms))
            {
                if (rdr.Read())
                {
                    counts = rdr.GetInt32(0);
                }
            }
            return new PageTools<T>(page, rows, counts, list);
        }

        private IList<Column> GetQueryColumn<T>(T queryModel) where T : BaseModel
        {
            IList<Column> columns = new List<Column>();
            if (queryModel != null)
            {
                ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
                IList<Column> allcolumns = sqlutil.GetModelProperty().GetColumns();
                foreach (Column c in allcolumns)
                {
                    if (queryModel.ContainsKey(c.Alias) && queryModel[c.Alias] != null)
                    {
                        columns.Add(c);
                    }
                }
            }
            return columns;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DateTime FindNowDate()
        {
            String sql = "select GETDATE() as nowdate";
            LogDebug(sql);
            return Convert.ToDateTime(SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql));
        }


        /// <summary>
        /// 查询记录数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryModel"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public int FindListCount<T>(T queryModel) where T : BaseModel
        {
            int count = 0;
            ModelSQLUtil sqlutil = GetModelSQLUtil(typeof(T));
            StringBuilder sql = new StringBuilder();
            sql.Append(sqlutil.GetSelectCountSQL());
            // WHERE
            StringBuilder where = new StringBuilder();
            SqlParameter[] parms = null;
            IList<Column> querycolumns = GetQueryColumn(queryModel);
            if (querycolumns.Count > 0) {
                where.Append(" WHERE ");
                parms = new SqlParameter[querycolumns.Count];
                for (int i = 0; i < querycolumns.Count; i++) {
                    Column c = querycolumns[i];
                    if (i > 0) where.Append(" AND ");
                    if (c.Type == DataType.String || c.Type == DataType.NText) {
                        where.Append(c.Name).Append(" LIKE '%' + @").Append(c.Alias).Append(" + '%'");
                    }
                    else {
                        where.Append(c.Name).Append("=@").Append(c.Alias);
                    }
                    parms[i] = sqlutil.GetSqlParameter(c, queryModel);
                }
            }
            sql.Append(where);            
            LogDebug(sql);
            LogDebug(parms);
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql.ToString(), parms)) {
                while (rdr.Read()) {
                    count = rdr.GetInt32(0);
                    break;
                }
            }
            return count;
        }        
    }
}
